package com.yenlien.traceyun.plant.dao;


import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.OKQENVData;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author jialong
 * @date 2021/03/31
 * @description:
 **/

public interface OKQEnvDataDao extends BaseJpaDao<OKQENVData> {




    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 ORDER BY c_time DESC LIMIT ?2", nativeQuery = true)
    List<OKQENVData> getLast(String equId, Integer limit);


    @Query(value = "SELECT param_name paramName, ANY_VALUE(param_unit) paramUnit FROM t_okq_data WHERE equipment_id = ?1 AND  param_name != '风向值' GROUP BY param_name", nativeQuery = true)
    List<JSONObject> getParamNameList(String equId);

    @Query(value = "SELECT param_name paramName, ANY_VALUE(param_unit) paramUnit FROM t_okq_data WHERE equipment_id = ?1  GROUP BY param_name", nativeQuery = true)
    List<JSONObject> getParamNameList2(String equId);


    @Query(value = "SELECT GROUP_CONCAT(CONCAT(param_name, '=', param_val)) FROM t_okq_data WHERE c_time IS NOT NULL AND equipment_id = ?1 GROUP BY c_time", nativeQuery = true)
    List<String> getByCreateTime(String equId);


    @Query(value = "SELECT GROUP_CONCAT(CONCAT(param_name, '=', param_val)) FROM t_okq_data WHERE c_time IS NOT NULL AND equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 GROUP BY c_time", nativeQuery = true)
    List<String> getByCreateTime(String equId, String startTime, String endTime);


    @Query(value = "SELECT param_val paramVal, c_time `time` FROM t_okq_data WHERE equipment_id = ?1 AND param_name = ?2", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName);

    @Query(value = "SELECT param_val paramVal, c_time `time` FROM t_okq_data WHERE equipment_id = ?1 AND param_name = ?2 AND c_time BETWEEN ?3 AND ?4", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName, String startTime, String endTime);


    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 ORDER BY c_time DESC LIMIT 20", nativeQuery = true)
    List<OKQENVData> getInsectHistoryData(String devId);


    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    List<OKQENVData> getHistoryData(String devId, String startTime, String endTime);



    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    Page<OKQENVData> getHistoryData(String devId, String startTime, String endTime, Pageable pageable);

    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 AND DATE_FORMAT(c_time, '%Y-%m-%d') = ?2 ORDER BY c_time DESC limit 20", nativeQuery = true)
    List<OKQENVData> getWaterData(String devId, String time);

    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1  AND param_name= ?2 AND c_time BETWEEN ?3 AND ?4", nativeQuery = true)
    List<OKQENVData> getHistoryData(String devId, String paramName, String startTime, String endTime);



    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    List<OKQENVData> findHistoryData(String devId, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 ORDER BY c_time DESC", nativeQuery = true)
    List<OKQENVData> getHistoryData(String devId);

    @Query(value = "SELECT * FROM t_okq_data WHERE equipment_id = ?1 ORDER BY c_time DESC", nativeQuery = true)
    Page<OKQENVData> getHistoryData(String devId, Pageable pageable);



    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1 AND type = ?2 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String devType);

    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1 AND type = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String devType, String fType);

    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_okq_data " +
            "WHERE date_format(c_time, '%Y-%m') = ?1 AND equipment_id = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysisEqu(String yearMonth, String equId, String fType);


    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_okq_data " +
            "WHERE date_format(c_time, '%Y-%m') = ?1 AND equipment_id = ?2 AND param_name = ?3 GROUP BY param_name ", nativeQuery = true)
    JSONObject getAnalysisName(String yearMonth, String equId, String paramName);


    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_okq_data " +
            "WHERE date_format(c_time, '%Y-%m-%d') = ?1 AND equipment_id = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysisEquDay(String yearMonth, String equId, String fType);


    @Query(value = "SELECT param_val paramVal, c_time cTime FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName);

    @Query(value = "SELECT param_val paramVal, c_time cTime FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2 AND company_id = ?3", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName, String fType);


    @Query(value = "SELECT param_val paramVal, param_name paramName, c_time cTime, equipment_id devId FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2 AND equipment_id = ?3", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String paramName, String equId);

    @Query(value = "SELECT param_val paramVal, param_name paramName, c_time cTime, equipment_id devId, param_unit unit FROM t_okq_data WHERE date_format(c_time, '%Y-%m') = ?1  AND equipment_id = ?2 ORDER BY c_time", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String equId);




}
